<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Penjurusan_model extends CI_Model{
	function __construct() {
		parent::__construct();
	}
	function get_all_cabang(){
		$this->db->select('*');
		$this->db->from('TbMDCabang');
		//$this->db->join('TblCOACabang','TbMDCabang.Nama=TblCOACabang.nama_cabang');
		$res_query = $this->db->get()->result_array();
		$nama = array('Semua'=>'Semua');
		foreach($res_query as $value){
			$nama['nama_cabang'][$value['Nama']]=$value['Nama'];
			//$nama['kode_cabang'][$value['Nama']]=$value['kode_coa'];
			$nama['kode_cabang'][$value['Nama']]=$value['Kode'];
			$nama['alamat_cabang'][$value['Nama']]=$value['Alamat'];
			$nama['telp_cabang'][$value['Nama']]=$value['Telp'];
		}

		return $nama;
	}
	function get_data_table($tgl_awal,$tgl_akhir){
		if($tgl_awal==''){
			$tgl_awal=date('Ymd');
		}else{
			$tgl_awal=$this->date_func->change_format_date($tgl_awal,'d-m-Y','Ymd');
		}
		if($tgl_akhir==''){
			$tgl_akhir=date('Ymd');
		}else{
			$tgl_akhir=$this->date_func->change_format_date($tgl_akhir,'d-m-Y','Ymd');
		}
		$string_query = '
			SELECT
			cabang_keberangkatan,
			SUM(total_penumpang) as total_penumpang,
			SUM(tiket_void) as tiket_void,
			SUM(total_omset) as total_omset,
			SUM(total_discount) as total_discount,
			SUM(fee_agen) as fee_agen,
			SUM(total_void) as total_void,
			SUM(total_setoran) as total_setoran,
			SUM(total_laba) as total_laba
			FROM TblSetoran
			WHERE tanggal BETWEEN "'.$tgl_awal.'" AND "'.$tgl_akhir.'"
			GROUP BY cabang_keberangkatan	
		';
		return $this->db->query($string_query)->result_array();
	}
	
	function get_biaya_langsung($tgl_awal,$tgl_akhir){
		if($tgl_awal==''){
			$tgl_awal=date('Ymd');
		}else{
			$tgl_awal=$this->date_func->change_format_date($tgl_awal,'d-m-Y','Ymd');
		}
		if($tgl_akhir==''){
			$tgl_akhir=date('Ymd');
		}else{
			$tgl_akhir=$this->date_func->change_format_date($tgl_akhir,'d-m-Y','Ymd');
		}
		$string_query = '
			SELECT
			TblSetoran.cabang_keberangkatan,
			SUM(jumlah) as biaya_langsung
			FROM TblBiaya
			INNER JOIN TblSetoran
			ON TblBiaya.NoSPJ=TblSetoran.NoSPJ
			WHERE is_tujuan=1 AND TblSetoran.tanggal BETWEEN "'.$tgl_awal.'" AND "'.$tgl_akhir.'"
			GROUP BY TblSetoran.cabang_keberangkatan	
		';
		$data = $this->db->query($string_query)->result_array();
		if(count($data)>0){
			for($i=0;$i<count($data);$i++){
				$retval[$data[$i]['cabang_keberangkatan']]=$data[$i]['biaya_langsung'];
			}
		}else{
			$retval = FALSE;
		}
		return $retval;
	}
	
	function get_biaya_opawal($tgl_awal,$tgl_akhir){
		if($tgl_awal==''){
			$tgl_awal=date('Ymd');
		}else{
			$tgl_awal=$this->date_func->change_format_date($tgl_awal,'d-m-Y','Ymd');
		}
		if($tgl_akhir==''){
			$tgl_akhir=date('Ymd');
		}else{
			$tgl_akhir=$this->date_func->change_format_date($tgl_akhir,'d-m-Y','Ymd');
		}
		$string_query = '
			SELECT
			TblSetoran.cabang_keberangkatan,
			SUM(jumlah) as biaya_opawal
			FROM TblBiaya
			INNER JOIN TblSetoran
			ON TblBiaya.NoSPJ=TblSetoran.NoSPJ
			WHERE is_tujuan=0 AND TblSetoran.tanggal BETWEEN "'.$tgl_awal.'" AND "'.$tgl_akhir.'"
			GROUP BY TblSetoran.cabang_keberangkatan	
		';
		$data = $this->db->query($string_query)->result_array();
		if(count($data)>0){
			for($i=0;$i<count($data);$i++){
				$retval[$data[$i]['cabang_keberangkatan']]=$data[$i]['biaya_opawal'];
			}
		}else{
			$retval=FALSE;
		}
		return $retval;
	}
	
}